#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : month_report_finance_channel.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-06-24 11:10 
@Desc    : 张立京_财务月需求
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import mail_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

if debug:
    receivers = 'dt@iyoujia.com'
else:
    receivers = 'zhanglijing@iyoujia.com;dt@iyoujia.com'


def process(st, et):
    sql = """   select A.城市,A.渠道,sum(A.GMV) GMV from (
                select 
                    whs.city_name 城市,
                    if(sc.name is null,'其他',sc.name ) 渠道,
                    round(sum(whc.income+whc.compensate_income)/100,2) GMV 
                    from youjia_report.wh_house_calendar whc
                    left join youjia_common.sales_channel sc on sc.id = whc.order_channel
                    left join youjia_report.wh_house_static whs on whs.house_id = whc.house_id 
                    where whc.dt between '{st}' and '{et}'
                    group by whs.city_name,sc.name
                ) A group by  A.城市,A.渠道
    """.format(st=st, et=et)

    channel_gmv_df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    channel_gmv_df = channel_gmv_df.set_index(['城市', '渠道'])
    log.info("channel_gmv_df.shape : {shape}".format(shape=channel_gmv_df.shape))
    channel_gmv_df = channel_gmv_df.unstack()

    sql2 = """select 
    whs.service_name  服务中心,
    -- group_concat(distinct whc.house_id),
    count(distinct if(whs.online_state=1,whs.house_id,null)) 房屋总数,
    sum(if(whc.online_stock = 1 and rent_stock = 1,1,0)) 可售间夜数, 
    sum(have_order) 已售间夜数, 
    concat(round(sum(have_order)/sum(if(whc.online_stock = 1 and rent_stock = 1,1,0))*100,2),'%%') 入住率,
    round(sum(whc.income+whc.compensate_income)/sum(have_order)/100) 客房RevPAR ,
    round(sum(whc.income+whc.compensate_income)/100,2) GMV（运营口径）
     
    from youjia_report.wh_house_calendar whc
    left join youjia_common.sales_channel sc on sc.id = whc.order_channel
    left join youjia_report.wh_house_static whs on whs.house_id = whc.house_id 
    where whc.dt between '{st}' and '{et}'
    group by whs.service_name 
    having whs.service_name  is not null and 房屋总数 > 0""".format(st=st, et=et)

    service_sale_df = pd.read_sql_query(sql2, db_util.youjia_13_sjz_db)
    service_sale_df = service_sale_df.set_index(['服务中心'])

    service_sale_df = service_sale_df.T

    sql3 = """select 
            whs.city_name 城市,
            whs.service_name 门店,
            whs.work_name 账号,
            whs.online_sale  线上销售,
            -- group_concat(distinct whc.house_id),
            count(distinct if(whs.online_state=1,whs.house_id,null)) 房屋总数,
            sum(whc.online_stock) 当月在线天数,
            sum(if(whc.online_stock = 1 and rent_stock = 1,1,0)) 可售间夜数, 
            concat(round(sum(if(whc.online_stock = 1 and rent_stock = 1,1,0))/sum(whc.online_stock)*100,2),"%%") 月可售率,
            sum(have_order) 当月入住间夜（含未支付）, 
            sum(if(whc.pay_state = 1,have_order,0)) 当月入住间夜, 
            concat(round(sum(have_order)/sum(if(whc.online_stock = 1 and rent_stock = 1,1,0))*100,2),'%%') 入住率（含未支付）,
            concat(round(sum(if(whc.pay_state = 1,have_order,0))/sum(if(whc.online_stock = 1 and rent_stock = 1,1,0))*100,2),'%%') 入住率,
            sum(if(whc.cancel_status <>0  ,have_order,0)) 入住间夜（部分退款订单）,
            round(sum(income/100),2) 入住收益（含未支付） , 
            round(sum(if(whc.pay_state = 1,income,0)/100),2) 入住收益, 
            round(sum(if(whc.cancel_status <>0 ,income,0)/100),2) 房费收益（部分退款订单）,	
            round(sum(compensate_income/100),2) 违约赔付收益 
             
            from youjia_report.wh_house_calendar whc
            left join youjia_common.sales_channel sc on sc.id = whc.order_channel
            left join youjia_report.wh_house_static whs on whs.house_id = whc.house_id 
            where whc.dt between '{st}' and '{et}'
            group by whs.city_name ,whs.service_name ,whs.work_name,whs.online_sale 
            having 房屋总数 > 0
             """.format(st=st, et=et)

    work_month_df = pd.read_sql_query(sql3, db_util.youjia_13_sjz_db)

    # excel_util.pd_to_excel({"渠道GMV": channel_gmv_df, "单店运营数据": service_sale_df, "门店月报": work_month_df},
    #                        '财务月需求')
    text = """    Dear All:
                    附件是渠道门店运营数据！ 
                    请查收！
                    谢谢！
            如有问题！
            请联系: dt@iyoujia.com
            """
    df_dict = {"渠道GMV": channel_gmv_df, "单店运营数据": service_sale_df, "门店月报": work_month_df}
    mail_util.distribute_mail(df_dict=df_dict, file_name='渠道门店运营数据', text=text,
                              title="渠道门店运营数据", receiver=receivers, na_rep='-', uuid='city')


def main():
    curdate = date_util.curdate()
    st = date_util.cur_month_first(1, curdate)
    et = date_util.cur_month_last(curdate)
    log.info("收件人为: {receivers}".format(receivers=receivers))
    log.info("获取的curdate为: {curdate}".format(curdate=curdate))
    log.info("开始时间为: {st}".format(st=st))
    log.info("结束时间为: {et}".format(et=et))
    process(st, et)


if __name__ == '__main__':
    main()
